package excel;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.Iterator;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

/**
 * @author suwy
 * @date 2014-2-17
 * @todo TODO
 */
public class Json2ExltableData {
	
	private static final Logger LOG = Logger.getLogger(Json2ExltableData.class);
	private static String fileName = "国家数据";
	private static String filePath = "I:/data/新康界抓取数据/"+fileName;
	private static String outputFile = "i:/data/新康界抓取数据/"+fileName+"tableData2.xlsx";
	
	private void translate() {
		StringBuilder content = new StringBuilder();
		FileOutputStream fOut = null;
		int tableDataPoint = 0;
		int beginPoint = 0;
		int endPoint = 0;
		try {
			Workbook workbook = new SXSSFWorkbook(2000);
			Sheet sheet = workbook.createSheet("sheet0");
			Row row = null;
			fOut = new FileOutputStream(outputFile);
			int rowNum = 0;
			for(int i = 304; i <= 641; i++) {
				content.append(readHtm(filePath + "/" + i + ".txt"));
				tableDataPoint = content.indexOf("\"tableData\":{},");
				if(tableDataPoint < 0) {
					beginPoint = content.toString().indexOf("{", 8);
					endPoint = content.toString().indexOf("}");
					JSONObject jsonObject = JSONObject.fromObject(content.toString().substring(beginPoint, endPoint+1));
						for (Iterator<String> iter = jsonObject.keys(); iter.hasNext();) {
							String key = (String) iter.next();
							row = sheet.createRow(rowNum);
							row.createCell(0).setCellValue(key);
							row.createCell(1).setCellValue(jsonObject.get(key).toString().trim());
							row.createCell(2).setCellValue(i);
							++rowNum;
						}
				}
				content.delete(0, content.length());
			}
			workbook.write(fOut);
		} catch (Exception e){
			LOG.info(e);
	} finally {
		if (null != fOut) {
			try {
				fOut.close();
				LOG.info("success export excel");
			} catch (Exception e) {
				e.printStackTrace(System.err);
			}
		}
	}
}
	
	static StringBuffer readHtm(String filePath){
		StringBuffer document = new StringBuffer();
		InputStreamReader fr = null;
		BufferedReader br = null;
		String line = null;
		try {
			fr = new InputStreamReader(new FileInputStream(filePath), "utf-8");
			br = new BufferedReader(fr);
			while ((line = br.readLine()) != null) {
				document.append(line);
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (br != null) {
					br.close();
				}
				if (fr != null) {
					fr.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		LOG.info("NO."+filePath);
		return document;
	}

	public static void main(String[] args) {
		Json2ExltableData instance = new Json2ExltableData();
		instance.translate();
	}

}
